﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Healthy
{
    public partial class frmdoctor : Form
    {
        int num;
        public frmdoctor()
        {
            InitializeComponent();
        }

        private void frmdoctor_Load(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);

            showType();
        }
        private void showType()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sqlType;
            sqlType = "select * from status ";

            SqlDataAdapter da;
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(sqlType, Conn);
            da.Fill(ds, "status");

            comboBox1.DataSource = ds.Tables["status"];
            comboBox1.ValueMember = "POSID";
            comboBox1.DisplayMember = "POSNM";
        }

        private void button1_Click(object sender, EventArgs e)
        {

            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            if (textBox1.Text != "" && cmblast.Text != "" && textBox3.Text != "" && comboBox1.Text != "")
            {
                try
                {
                    string query = "insert into doctor (DCID,DCLED,DCNAM,POSID,ADDRESS1,TEL,EMAIL) values('" + textBox1.Text + "','" + cmblast.Text + "','" + textBox3.Text + "','" + comboBox1.SelectedValue + "','" + maskedTextBox1.Text + "','" + textBox4.Text + "','" + textBox5.Text + "')";
                    SqlCommand cmd7 = new SqlCommand(query, Conn);
                    cmd7.ExecuteNonQuery();
                    
                    string sql1 = "SELECT DCID,DCLED,DCNAM,POSNM,TEL,EMAIL,ADDRESS1 FROM doctor,status where doctor.POSID =status.POSID";
                    SqlCommand cmd1 = new SqlCommand(sql1, Conn);
                    SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
                    DataSet ds = new DataSet();
                   

                    odbcDA.Fill(ds);
                    //cmd.ExecuteReader();
                    this.dgvdc.DataSource = ds.Tables[0];

                    DataGridViewCellStyle cs = new DataGridViewCellStyle();
                    cs.Font = new Font("Ms Sans Serif", 12, FontStyle.Bold);
                    this.dgvdc.ColumnHeadersDefaultCellStyle = cs;
                    this.dgvdc.Columns[0].HeaderText = "รหัส";
                    this.dgvdc.Columns[1].HeaderText = "คำนำ";
                    this.dgvdc.Columns[2].HeaderText = "ชื่อ";
                    this.dgvdc.Columns[3].HeaderText = "ตำแหน่ง";
                    this.dgvdc.Columns[4].HeaderText = "เบอร์โทร";
                    this.dgvdc.Columns[5].HeaderText = "อีเมลล์";
                    this.dgvdc.Columns[6].HeaderText = "ที่อยู่";

                    this.dgvdc.Columns[0].Width = 100;
                    this.dgvdc.Columns[1].Width = 100;
                    this.dgvdc.Columns[2].Width = 150;
                    this.dgvdc.Columns[3].Width = 150;
                    this.dgvdc.Columns[4].Width = 100;
                    this.dgvdc.Columns[5].Width = 100;
                    this.dgvdc.Columns[6].Width = 250;
                }
                catch (Exception errToAdd)
                {
                    //  showgrid();

                    MessageBox.Show("ข้อมูลผิดพลาดเนื่องจาก" + errToAdd.Message, "ผิดพลาด");
                }
            }
            else
            {
                MessageBox.Show("ข้อมูลไม่ครบ", "ผิดพลาด");
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string query = "delete from doctor where DCID='" + textBox1.Text + "'";
            SqlCommand cmd = new SqlCommand(query, Conn);
            cmd.ExecuteNonQuery();

            string sql = "SELECT * FROM doctor ";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();

            //Conn.Open();
            odbcDA.Fill(ds);
            //cmd.ExecuteReader();
            this.dgvdc.DataSource = ds.Tables[0];

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 12, FontStyle.Regular);
            this.dgvdc.Columns[0].HeaderText = "รหัส";
            this.dgvdc.Columns[1].HeaderText = "คำนำ";
            this.dgvdc.Columns[2].HeaderText = "ชื่อ";
            this.dgvdc.Columns[3].HeaderText = "ตำแหน่ง";
            this.dgvdc.Columns[4].HeaderText = "เบอร์โทร";
            this.dgvdc.Columns[5].HeaderText = "อีเมลล์";
            this.dgvdc.Columns[6].HeaderText = "ที่อยู่";

            this.dgvdc.Columns[0].Width = 100;
            this.dgvdc.Columns[1].Width = 100;
            this.dgvdc.Columns[2].Width = 150;
            this.dgvdc.Columns[3].Width = 150;
            this.dgvdc.Columns[4].Width = 100;
            this.dgvdc.Columns[5].Width = 100;
            this.dgvdc.Columns[6].Width = 250;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            if (textBox1.Text == "" || cmblast.Text == "" || textBox3.Text == "" || comboBox1.Text == "" || maskedTextBox1.Text == "" || textBox4.Text == "" || textBox5.Text == "")
            {
                MessageBox.Show("กรุณาป้อนข้อมูลให้ครบ!!!", "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Information);
                textBox1.Focus();
                textBox1.SelectAll();
                return;
            }

            string sqlupdate = "update doctor set DCID='" + textBox1.Text + "',DCLED='" + cmblast.Text + "',DCNAM='" + textBox3.Text + "',POSID='" + comboBox1.SelectedValue + "',TEL='" + maskedTextBox1 .Text+ "',EMAIL='" + textBox4.Text + "', ADDRESS1='" + textBox5.Text + "' where DCID='" + textBox1.Text + "'";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sqlupdate;
            cmd.Connection = Conn;
            cmd.ExecuteNonQuery();
            ShowDoc();

            textBox1.Text = "";
            cmblast.Text = "";
            textBox3.Text = "";
            comboBox1.Text = "";
            maskedTextBox1.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
        }
        private void ShowDoc()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();
            string sql = "SELECT DCID,DCLED,DCNAM,POSNM,TEL,EMAIL,ADDRESS1 FROM doctor,status where DCID = '" + textBox1.Text + "'";
            //string sql = "select * from doctor Where DCID = '" + textBox1.Text + "'";
            SqlCommand cmd1 = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();

            odbcDA.Fill(ds, "doctor");
            num = ds.Tables["doctor"].Rows.Count;

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);
            this.dgvdc.Columns[0].HeaderText = "รหัส";
            this.dgvdc.Columns[1].HeaderText = "คำนำ";
            this.dgvdc.Columns[2].HeaderText = "ชื่อ";
            this.dgvdc.Columns[3].HeaderText = "ตำแหน่ง";
            this.dgvdc.Columns[4].HeaderText = "เบอร์โทร";
            this.dgvdc.Columns[5].HeaderText = "อีเมลล์";
            this.dgvdc.Columns[6].HeaderText = "ที่อยู่";

            this.dgvdc.Columns[0].Width = 100;
            this.dgvdc.Columns[1].Width = 100;
            this.dgvdc.Columns[2].Width = 150;
            this.dgvdc.Columns[3].Width = 150;
            this.dgvdc.Columns[4].Width = 100;
            this.dgvdc.Columns[5].Width = 100;
            this.dgvdc.Columns[6].Width = 250;
        }
        private void button49_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sql = "SELECT DCID,DCLED,DCNAM,POSNM,TEL,EMAIL,ADDRESS1 FROM doctor,status where doctor.POSID =status.POSID ";
            SqlCommand cmd = new SqlCommand(sql, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            //Conn.Open();
            odbcDA.Fill(ds);
            //cmd.ExecuteReader();
            this.dgvdc.DataSource = ds.Tables[0];

            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);
            this.dgvdc.Columns[0].HeaderText = "รหัส";
            this.dgvdc.Columns[1].HeaderText = "คำนำ";
            this.dgvdc.Columns[2].HeaderText = "ชื่อ";
            this.dgvdc.Columns[3].HeaderText = "ตำแหน่ง";
            this.dgvdc.Columns[4].HeaderText = "เบอร์โทร";
            this.dgvdc.Columns[5].HeaderText = "อีเมลล์";
            this.dgvdc.Columns[6].HeaderText = "ที่อยู่";

            this.dgvdc.Columns[0].Width = 100;
            this.dgvdc.Columns[1].Width = 100;
            this.dgvdc.Columns[2].Width = 150;
            this.dgvdc.Columns[3].Width = 150;
            this.dgvdc.Columns[4].Width = 100;
            this.dgvdc.Columns[5].Width = 100;
            this.dgvdc.Columns[6].Width = 250;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void dgvdc_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {
            textBox1.Text = Convert.ToString(dgvdc.Rows[e.RowIndex].Cells[0].Value);
            cmblast.Text = Convert.ToString(dgvdc.Rows[e.RowIndex].Cells[1].Value);
            textBox3.Text = Convert.ToString(dgvdc.Rows[e.RowIndex].Cells[2].Value);
            comboBox1.Text = Convert.ToString(dgvdc.Rows[e.RowIndex].Cells[3].Value);  
             maskedTextBox1.Text = Convert.ToString(dgvdc.Rows[e.RowIndex].Cells[4].Value);
            textBox4.Text = Convert.ToString(dgvdc.Rows[e.RowIndex].Cells[5].Value); 
           textBox5.Text = Convert.ToString(dgvdc.Rows[e.RowIndex].Cells[6].Value);
        }
    }
}
